﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace HRDexCapstoneProject.Functions.Announcement
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        //declare table
        DataTable dtbl = new DataTable("dtbl");
        
        protected void Page_Load(object sender, EventArgs e)
        {
            
            //Announcement Output Data
            string announcementID = Request.QueryString["AnnouncementID"];
                  
            if (announcementID != null)
            {
                 string sqlc = "SELECT formAnnouncement.Subject, formAnnouncement.Body, formAnnouncement.DateTime," + 
                " formAnnouncement.Attachment, catAnnouncement.CatName " +
                " FROM formAnnouncement INNER JOIN catAnnouncement ON formAnnouncement.CatAnnID = catAnnouncement.CatAnnID " +
                "   WHERE        (formAnnouncement.AnnouncementID = " + announcementID + ")";
            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            cmd.Fill(dtbl);
            lblID.Text = announcementID;
            lblAttach.Text = dtbl.Rows[0].ItemArray[3].ToString();
            lblDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
            lblPubDate.Text = dtbl.Rows[0].ItemArray[2].ToString();
            lblTitle.Text = dtbl.Rows[0].ItemArray[0].ToString();
            lblType.Text = dtbl.Rows[0].ItemArray[4].ToString();

            }

           
           

            
        }



        protected void txtSubmit_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            //connection Add data
            SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=HRdexDB; Integrated Security=SSPI");
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();


            if (txtAnnouncementID.Text != announcementID)
            {
                conn.Open();
                //Add 

                SqlCommand cmd = new SqlCommand("Insert Into formAnnouncement (Subject, Body, CatAnnID, Attachment, DateTime) Values (@subject, @body, @catannID, @attachment, @datetime )", conn);
                Stream fs = fuAttachment.PostedFile.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = txtSubject.Text;
                cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = txtDescription.Text;
                cmd.Parameters.Add("@catannID", SqlDbType.Int).Value = ddlCategories.Text;
                cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
                cmd.Parameters.Add("@datetime", SqlDbType.DateTime).Value = DateTime.Now;

                cmd.ExecuteNonQuery();

                conn.Close();
                fuAttachment.Visible = true;
                lblDownload.Visible = false;
                btnDownload.Visible = false;
            }
                else
            {
                EditHere();
                uploadedit();
            }
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            //this.cmdStore.Click += new System.EventHandler(this.cmdStore_Click);
            this.Load += new System.EventHandler(this.Page_Load);
            
        }

        protected void btnEdit_Click(object sender, ImageClickEventArgs e)
        {
            pAddEdit.Visible = true;
            pOutput.Visible = false;

            string announcementID = Request.QueryString["AnnouncementID"];


            txtAnnouncementID.Text = announcementID;
            txtSubject.Text = dtbl.Rows[0].ItemArray[0].ToString();
            txtDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
            //ddlCategories.Text = dtbl.Rows[0].ItemArray[4].ToString();
            }

         public void EditHere()
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string edit = ("UPDATE [formAnnouncement] SET [Subject] ='" + txtSubject.Text + "', [Body] ='" + txtDescription.Text + "' WHERE  ([AnnouncementID] = " + announcementID + ")");
            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(edit, conn);
            SqlDataAdapter adapt = new SqlDataAdapter();
            conn.Open();


            cmd.Parameters.AddWithValue("Subject", txtSubject.Text);
            cmd.Parameters.AddWithValue("Body", txtDescription.Text);
            //cmd.Parameters.Add("CatAnnID", ddlCategories.Text);

            cmd.ExecuteNonQuery();
            conn.Close();
            fuAttachment.Visible = false;
            //lblDownload.Visible = true;
            //btnDownload.Visible = true;

        }

         private void uploadedit()
         {
             string filepath = fuAttachment.PostedFile.FileName;
             string filename = Path.GetFileName(filepath);
             lblDownload.Text = filename;

             Stream fs = fuAttachment.PostedFile.InputStream;
             BinaryReader br = new BinaryReader(fs);
             Byte[] bytes = br.ReadBytes((Int32)fs.Length);

             string announcementID = Request.QueryString["AnnouncementID"];
             string edit = ("UPDATE formAnnouncement SET  Attachment = @Data WHERE  ([AnnouncementID] = " + announcementID + ")");
             SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
             SqlCommand cmd = new SqlCommand(edit, conn);
             cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
             SqlDataAdapter adapt = new SqlDataAdapter();

             conn.Open();

             cmd.ExecuteNonQuery();
             conn.Close();


         }

         protected void btnDownload_Click(object sender, EventArgs e)
         {
             string announcementID = Request.QueryString["AnnouncementID"];
             string strQuery = "SELECT Attachment, Subject FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
             SqlCommand cmd = new SqlCommand(strQuery);
             DataTable dt = GetData(cmd);
             if (dt != null)
             {
                 download(dt);
             }
         }
         SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
         private DataTable GetData(SqlCommand cmd)
         {
             DataTable dt = new DataTable();
             SqlDataAdapter sda = new SqlDataAdapter();
             cmd.CommandType = CommandType.Text;
             cmd.Connection = conn;
             try
             {
                 conn.Open();
                 sda.SelectCommand = cmd;
                 sda.Fill(dt);
                 return dt;

             }
             catch
             {
                 return null;
             }
             finally
             {
                 conn.Close();
                 sda.Dispose();
                 conn.Dispose();
             }

         }
         private void download(DataTable dt)
         {
             Byte[] bytes = (Byte[])dt.Rows[0]["Attachment"];
             Response.Buffer = true;
             Response.Charset = "";
             Response.Cache.SetCacheability(HttpCacheability.NoCache);
             //Response.ContentType = dt.Rows[0]["FileType"].ToString();
             Response.AddHeader("content-disposition", "attachment;FileName="
             + dt.Rows[0]["Subject"].ToString());
             Response.BinaryWrite(bytes);
             Response.Flush();
             Response.End();
         }
    }
}